

---
--- CREATE TABLE: positions
---
CREATE TABLE POSITIONS
(
	ID SMALLINT NOT NULL,
	NAME VARCHAR2(40) NOT NULL,
	PRIMARY KEY (ID)
)
GO

---
--- CREATE TABLE: system_roles
---
CREATE TABLE SYSTEM_ROLES
(
	ID SMALLINT NOT NULL,
	NAME VARCHAR2(40) NOT NULL,
	DESCRIPTION VARCHAR2(250),
	PRIMARY KEY (ID)
)
GO

---
--- CREATE TABLE: users
---
CREATE TABLE USERS
(
	ID INTEGER NOT NULL,
	LOGIN VARCHAR2(20) NOT NULL,
	HASHED_PASSWORD VARCHAR2(50) NOT NULL,
	PESEL NUMBER,
	EMAIL VARCHAR2(320),
	POSITION_ID SMALLINT NOT NULL,
	ROLE_ID SMALLINT NOT NULL,
	STUDY_PERIOD VARCHAR2(20),
	ACTIVATED CHAR NOT NULL,
	EXPIRY_DATE DATE NOT NULL,
	SALT VARCHAR2(50) NOT NULL,
	PRIMARY KEY (ID)
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: users_ibfk_1
---
ALTER TABLE USERS ADD 
	CONSTRAINT users_ibfk_1 FOREIGN KEY (POSITION_ID)
		REFERENCES POSITIONS (ID)
		ON DELETE SET NULL	
			
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: users_ibfk_2
---
ALTER TABLE USERS ADD 
	CONSTRAINT users_ibfk_2 FOREIGN KEY (ROLE_ID)
		REFERENCES SYSTEM_ROLES (ID)
		ON DELETE CASCADE
GO


---
--- CREATE TABLE: surveys
---
CREATE TABLE SURVEYS
(
	ID INTEGER NOT NULL,
	TITLE VARCHAR2(50) NOT NULL,
	DESCRIPTION VARCHAR2(250) NOT NULL,
	CREATION_DATE DATE NOT NULL,
	START_DATE DATE,
	END_DATE DATE,
	ANONYMOUS CHAR,
	READY CHAR NOT NULL,
	USER_ID INTEGER,
	PRIMARY KEY (ID)
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: surveys_ibfk_1
---
ALTER TABLE SURVEYS ADD 
	CONSTRAINT surveys_ibfk_1 FOREIGN KEY (USER_ID)
		REFERENCES USERS (ID)
		ON DELETE SET NULL	
GO


---
--- CREATE TABLE: question_types
---
CREATE TABLE QUESTION_TYPES
(
	ID SMALLINT NOT NULL,
	NAME VARCHAR2(40) NOT NULL,
	DESCRIPTION VARCHAR2(250),
	PRIMARY KEY (ID)
)
GO

---
--- CREATE TABLE: questions
---
CREATE TABLE QUESTIONS
(
	ID INTEGER NOT NULL,
	SURVEY_ID INTEGER NOT NULL,
	TYPE_ID SMALLINT NOT NULL,
	QUESTION VARCHAR2(400) NOT NULL,
	PRIMARY KEY (ID)
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: questions_ibfk_1
---
ALTER TABLE QUESTIONS ADD 
	CONSTRAINT questions_ibfk_1 FOREIGN KEY (SURVEY_ID)
		REFERENCES SURVEYS (ID)
		ON DELETE CASCADE
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: questions_ibfk_2
---
ALTER TABLE QUESTIONS ADD 
	CONSTRAINT questions_ibfk_2 FOREIGN KEY (TYPE_ID)
		REFERENCES QUESTION_TYPES (ID)
				ON DELETE CASCADE
GO

---
--- CREATE TABLE: arranging_questions
---
CREATE TABLE ARRANGING_QUESTIONS
(
	ID INTEGER NOT NULL,
	QUESTION_ID INTEGER NOT NULL,
	NUMBER_OF_ANSWERS INTEGER NOT NULL,
	PRIMARY KEY (ID)
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: arranging_questions_ibfk_1
---
ALTER TABLE ARRANGING_QUESTIONS ADD 
	CONSTRAINT arranging_questions_ibfk_1 FOREIGN KEY (QUESTION_ID)
		REFERENCES QUESTIONS (ID)
				ON DELETE CASCADE
GO

---
--- CREATE TABLE: aq_possible_answers
---
CREATE TABLE AQ_POSSIBLE_ANSWERS
(
	ID INTEGER NOT NULL,
	POSSIBLE_ANSWER VARCHAR2(400) NOT NULL,
	ARRANGING_QUESTION_ID INTEGER NOT NULL,
	PRIMARY KEY (ID)
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: aq_possible_answers_ibfk_1
---
ALTER TABLE AQ_POSSIBLE_ANSWERS ADD 
	CONSTRAINT aq_possible_answers_ibfk_1 FOREIGN KEY (ARRANGING_QUESTION_ID)
		REFERENCES ARRANGING_QUESTIONS (ID)
				ON DELETE CASCADE
GO

---
--- CREATE TABLE: distrbuting_questions
---
CREATE TABLE DISTRBUTING_QUESTIONS
(
	ID INTEGER NOT NULL,
	QUESTION_ID INTEGER NOT NULL,
	SUM_OF_POINTS INTEGER NOT NULL,
	PRIMARY KEY (ID)
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: distrbuting_questions_ibfk_1
---
ALTER TABLE DISTRBUTING_QUESTIONS ADD 
	CONSTRAINT distrbuting_questions_ibfk_1 FOREIGN KEY (QUESTION_ID)
		REFERENCES QUESTIONS (ID)
				ON DELETE CASCADE
GO

---
--- CREATE TABLE: dq_posiible_answers
---
CREATE TABLE DQ_POSIIBLE_ANSWERS
(
	ID INTEGER NOT NULL,
	POSSIBLE_ANSWER VARCHAR2(400) NOT NULL,
	DISTRIBUTING_QUESTION_ID INTEGER,
	PRIMARY KEY (ID)
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: dq_posiible_answers_ibfk_1
---
ALTER TABLE DQ_POSIIBLE_ANSWERS ADD 
	CONSTRAINT dq_posiible_answers_ibfk_1 FOREIGN KEY (DISTRIBUTING_QUESTION_ID)
		REFERENCES DISTRBUTING_QUESTIONS (ID)
				ON DELETE CASCADE
GO



---
--- CREATE TABLE: filled_surveys
---
CREATE TABLE FILLED_SURVEYS
(
	ID INTEGER NOT NULL,
	CREATION_DATE DATE NOT NULL,
	USER_ID NUMBER NOT NULL,
	SURVEY_ID INTEGER NOT NULL,
	READY CHAR NOT NULL,
	PRIMARY KEY (ID)
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: filled_surveys_ibfk_1
---
ALTER TABLE FILLED_SURVEYS ADD 
	CONSTRAINT filled_surveys_ibfk_1 FOREIGN KEY (SURVEY_ID)
		REFERENCES surveys (ID)
				ON DELETE CASCADE
GO

---
--- CREATE TABLE: rating_questions
---
CREATE TABLE RATING_QUESTIONS
(
	ID INTEGER NOT NULL,
	QUESTION_ID INTEGER NOT NULL,
	MIN_RATE INTEGER NOT NULL,
	MAX_RATE INTEGER NOT NULL,
	PRIMARY KEY (ID)
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: rating_questions_ibfk_1
---
ALTER TABLE RATING_QUESTIONS ADD 
	CONSTRAINT rating_questions_ibfk_1 FOREIGN KEY (QUESTION_ID)
		REFERENCES QUESTIONS (ID)
				ON DELETE CASCADE
GO

---
--- CREATE TABLE: rq_answers
---
CREATE TABLE RQ_ANSWERS
(
	ID INTEGER NOT NULL,
	FILLED_SURVEY_ID INTEGER NOT NULL,
	RATE_GIVEN INTEGER NOT NULL,
	RATING_QUESTION_ID INTEGER NOT NULL,
	PRIMARY KEY (ID)
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: rq_answers_ibfk_1
---
ALTER TABLE RQ_ANSWERS ADD 
	CONSTRAINT rq_answers_ibfk_1 FOREIGN KEY (FILLED_SURVEY_ID)
		REFERENCES FILLED_SURVEYS (ID)
				ON DELETE CASCADE
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: rq_answers_ibfk_2
---
ALTER TABLE RQ_ANSWERS ADD 
	CONSTRAINT rq_answers_ibfk_2 FOREIGN KEY (RATING_QUESTION_ID)
		REFERENCES RATING_QUESTIONS (ID)
				ON DELETE CASCADE
GO

---
--- CREATE TABLE: closed_questions
---
CREATE TABLE CLOSED_QUESTIONS
(
	ID INTEGER NOT NULL,
	QUESTION_ID INTEGER NOT NULL,
	MIN_ANSWERS INTEGER NOT NULL,
	MAX_ANSWERS INTEGER NOT NULL,
	PRIMARY KEY (ID)
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: closed_questions_ibfk_1
---
ALTER TABLE CLOSED_QUESTIONS ADD 
	CONSTRAINT closed_questions_ibfk_1 FOREIGN KEY (QUESTION_ID)
		REFERENCES QUESTIONS (ID)
				ON DELETE CASCADE
GO

---
--- CREATE TABLE: survey_permission_types
---
CREATE TABLE SURVEY_PERMISSION_TYPES
(
	ID SMALLINT NOT NULL,
	NAME VARCHAR2(40) NOT NULL,
	DESCRIPTION VARCHAR2(250),
	PRIMARY KEY (ID)
)
GO

---
--- CREATE TABLE: users_surveys_permissions
---
CREATE TABLE USERS_SURVEYS_PERMISSIONS
(
	ID INTEGER NOT NULL,
	USER_ID INTEGER,
	SURVEY_ID INTEGER,
	PERMISSION_TYPE_ID SMALLINT,
	PRIMARY KEY (ID)
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: users_surveys_permissions_ibfk_1
---
ALTER TABLE USERS_SURVEYS_PERMISSIONS ADD 
	CONSTRAINT users_surveys_permissions_ibfk_1 FOREIGN KEY (USER_ID)
		REFERENCES USERS (ID)
				ON DELETE CASCADE
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: users_surveys_permissions_ibfk_2
---
ALTER TABLE USERS_SURVEYS_PERMISSIONS ADD 
	CONSTRAINT users_surveys_permissions_ibfk_2 FOREIGN KEY (SURVEY_ID)
		REFERENCES SURVEYS (ID)
				ON DELETE CASCADE
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: users_surveys_permissions_ibfk_3
---
ALTER TABLE USERS_SURVEYS_PERMISSIONS ADD 
	CONSTRAINT users_surveys_permissions_ibfk_3 FOREIGN KEY (PERMISSION_TYPE_ID)
		REFERENCES SURVEY_PERMISSION_TYPES (ID)
				ON DELETE CASCADE
GO

---
--- CREATE TABLE: oq_answers
---
CREATE TABLE OQ_ANSWERS
(
	ID INTEGER NOT NULL,
	FILLED_SURVEY_ID INTEGER NOT NULL,
	ANSWER VARCHAR2(400) NOT NULL,
	QUESTION_ID INTEGER NOT NULL,
	PRIMARY KEY (ID)
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: oq_answers_ibfk_1
---
ALTER TABLE OQ_ANSWERS ADD 
	CONSTRAINT oq_answers_ibfk_1 FOREIGN KEY (FILLED_SURVEY_ID)
		REFERENCES FILLED_SURVEYS (ID)
				ON DELETE CASCADE
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: oq_answers_ibfk_2
---
ALTER TABLE OQ_ANSWERS ADD 
	CONSTRAINT oq_answers_ibfk_2 FOREIGN KEY (QUESTION_ID)
		REFERENCES QUESTIONS (ID)
				ON DELETE CASCADE
GO

---
--- CREATE TABLE: dq_answers
---
CREATE TABLE DQ_ANSWERS
(
	ID INTEGER NOT NULL,
	FILLED_SURVEY_ID INTEGER NOT NULL,
	POINTS_GIVEN INTEGER NOT NULL,
	POSSIBLE_ANSWER_ID INTEGER NOT NULL,
	PRIMARY KEY (ID)
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: dq_answers_ibfk_1
---
ALTER TABLE DQ_ANSWERS ADD 
	CONSTRAINT dq_answers_ibfk_1 FOREIGN KEY (FILLED_SURVEY_ID)
		REFERENCES FILLED_SURVEYS (ID)
				ON DELETE CASCADE
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: dq_answers_ibfk_2
---
ALTER TABLE DQ_ANSWERS ADD 
	CONSTRAINT dq_answers_ibfk_2 FOREIGN KEY (POSSIBLE_ANSWER_ID)
		REFERENCES DQ_POSIIBLE_ANSWERS (ID)
				ON DELETE CASCADE
GO

---
--- CREATE TABLE: aq_answers
---
CREATE TABLE AQ_ANSWERS
(
	ID INTEGER NOT NULL,
	FILLED_SURVEY_ID INTEGER NOT NULL,
	POSITION_GIVEN INTEGER NOT NULL,
	POSSIBLE_ANSWER_ID INTEGER NOT NULL,
	PRIMARY KEY (ID)
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: aq_answers_ibfk_1
---
ALTER TABLE AQ_ANSWERS ADD 
	CONSTRAINT aq_answers_ibfk_1 FOREIGN KEY (FILLED_SURVEY_ID)
		REFERENCES FILLED_SURVEYS (ID)
				ON DELETE CASCADE
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: aq_answers_ibfk_2
---
ALTER TABLE AQ_ANSWERS ADD 
	CONSTRAINT aq_answers_ibfk_2 FOREIGN KEY (POSSIBLE_ANSWER_ID)
		REFERENCES AQ_POSSIBLE_ANSWERS (ID)
				ON DELETE CASCADE
GO

---
--- CREATE TABLE: cq_possible_answers
---
CREATE TABLE CQ_POSSIBLE_ANSWERS
(
	ID INTEGER NOT NULL,
	POSSIBLE_ANSWER VARCHAR2(400) NOT NULL,
	CLOSED_QUESTION_ID INTEGER NOT NULL,
	PRIMARY KEY (ID)
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: cq_possible_answers_ibfk_1
---
ALTER TABLE CQ_POSSIBLE_ANSWERS ADD 
	CONSTRAINT cq_possible_answers_ibfk_1 FOREIGN KEY (CLOSED_QUESTION_ID)
		REFERENCES CLOSED_QUESTIONS (ID)
				ON DELETE CASCADE
GO

---
--- CREATE TABLE: cq_answers
---
CREATE TABLE CQ_ANSWERS
(
	ID INTEGER NOT NULL,
	FILLED_SURVEY_ID INTEGER NOT NULL,
	POSSIBLE_ANSWER_ID INTEGER NOT NULL,
	PRIMARY KEY (ID)
)
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: cq_answers_ibfk_1
---
ALTER TABLE CQ_ANSWERS ADD 
	CONSTRAINT cq_answers_ibfk_1 FOREIGN KEY (FILLED_SURVEY_ID)
		REFERENCES FILLED_SURVEYS (ID)
				ON DELETE CASCADE
GO

---
--- CREATE FOREIGN KEY CONSTRAINT: cq_answers_ibfk_2
---
ALTER TABLE CQ_ANSWERS ADD 
	CONSTRAINT cq_answers_ibfk_2 FOREIGN KEY (POSSIBLE_ANSWER_ID)
		REFERENCES CQ_POSSIBLE_ANSWERS (ID)
				ON DELETE CASCADE
		
GO
